package db;
import java.sql.*;
import java.util.ArrayList;
//import java.util.Date;
import model.User;
import model.MessageTable;

public class DB {
	Connection ct;
	PreparedStatement pstmt;

	public DB(){
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=JSP", "sa", "123456");
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public User checkUser(String username,String password){
		try{
			pstmt=ct.prepareStatement("select * from [userTable] where username=? and password=?");
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			ResultSet rs=pstmt.executeQuery();
			User user=new User();
			while(rs.next()){
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				return user;
			}
			return null;
		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public ArrayList findMessageInfo(){
		try{
			ArrayList al=new ArrayList();
			pstmt=ct.prepareStatement("select * from [MessageTable]");
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()){
				MessageTable mt=new MessageTable();
				mt.setId(rs.getInt(1));
				mt.setUserId(rs.getInt(2));
				mt.setDate(rs.getDate(3));
				mt.setTitle(rs.getString(4));
				mt.setContent(rs.getString(5));
				al.add(mt);
			}
			return al;
		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public String getUserName(int id){
		String username=null;
		try{
			pstmt=ct.prepareStatement("select username from [userTable] where id=?");
			pstmt.setInt(1, id);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()){
				username=rs.getString(1);
			}
			return username;
		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean addInfo(MessageTable mt){
		try{
			pstmt=ct.prepareStatement("insert into [MessageTable] values(?,?,?,?)");
			pstmt.setInt(1, mt.getUserId());
			pstmt.setDate(2, mt.getDate());
			pstmt.setString(3, mt.getTitle());
			pstmt.setString(4, mt.getContent());
			pstmt.executeUpdate();
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean insertUser(String username,String pwd){
		try{
			pstmt=ct.prepareStatement("insert into [userTable] values(?,?)");
			pstmt.setString(1, username);
			pstmt.setString(2, pwd);
			pstmt.executeUpdate();
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
			return false;
		}
	}
}
